#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
--每天/每月/每年线上线下以及新老学员的意向用户个数
insert into hive.ol_edu_rpt.rpt_intent_count
select
        '2023-09-22' as date_time,
        "day",
        "month",
        "year",
        time_type,
        clue_state,
        origin_type,
        intent_avl_count
from hive.ol_edu_dm.dm_intent;

--每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
insert into hive.ol_edu_rpt.rpt_intent_area_count
select
        '2023-09-22' as date_time,
        "day",
        "month",
        "year",
        time_type,
        area,
        clue_state,
        origin_type,
        intent_avl_count
from hive.ol_edu_dm.dm_intent;

--每天/每月/每年各学科线上线下以及新老学员的意向用户个数
insert into hive.ol_edu_rpt.rpt_intent_subject_count
select
        '2023-09-22' as date_time,
        "day",
        "month",
        "year",
        time_type,
         itcast_subject_id,
        itcast_subject,
        clue_state,
        origin_type,
        intent_avl_count
from hive.ol_edu_dm.dm_intent;

--每天/每月/每年各校区线上线下以及新老学员的意向用户个数
insert into hive.ol_edu_rpt.rpt_intent_school_count
select
        '2023-09-22' as date_time,
        "day",
        "month",
        "year",
        time_type,
        itcast_school_id,
        itcast_school,
        clue_state,
        origin_type,
        intent_avl_count
from hive.ol_edu_dm.dm_intent;

--每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
insert into hive.ol_edu_rpt.rpt_intent_origin_count
select
        '2023-09-22' as date_time,
        "day",
        "month",
        "year",
        time_type,
        origin_channel,
        clue_state,
        origin_type,
        intent_avl_count
from hive.ol_edu_dm.dm_intent;

--每天/每月/每年各咨询中心线上线下以及新老学员的意向用户个数
insert into hive.ol_edu_rpt.rpt_intent_depart_count
select
        '2023-09-22' as date_time,
        "day",
        "month",
        "year",
        time_type,
        department_id,
        department_name,
        clue_state,
        origin_type,
        intent_avl_count
from hive.ol_edu_dm.dm_intent;

--每天线上线下及新老学员的有效线索个数
insert into hive.ol_edu_rpt.rpt_clue_day_count
select
        '2023-09-22' as date_time,
        "day",
        time_type,
        clue_state,
        origin_type,
        clue_avl_count
from hive.ol_edu_dm.dm_clue;

--每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
insert into hive.ol_edu_rpt.rpt_clue_hour_rate
select
        '2023-09-22' as date_time,
        "hour",
        group_type,
        clue_state,
        origin_type,
        cast(cast(clue_avl_count as decimal(38,5)) / if(cast (clue_total_count as decimal(38,5))<>0,clue_total_count,1)*100 as decimal(5,2)) as eff_con_rate
from hive.ol_edu_dws.dws_clue_hourcount;
"